package zipcode;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

import javax.sql.DataSource;

import common.misc.StringMisc;

/*
    seq NUMBER( 5 ) NOT NULL,
    zipcode CHAR( 7 ) NOT NULL,
    sido CHAR( 4 ) NOT NULL,
    gugun VARCHAR2( 100 ) NOT NULL,
    dong VARCHAR2( 200 ) NOT NULL,
    ri VARCHAR2( 1000 ),
    bunji VARCHAR2( 150 )
*/
public class ZipcodeDAOOracle {


    private DataSource jdbcFactory;

    public ZipcodeDAOOracle() throws NamingException {

        Context context = new InitialContext();
        jdbcFactory = ( DataSource )context.lookup( "java:comp/env/jdbc/Oracle11g" );

    } // end method

	public ArrayList getZipList(String dong) throws SQLException{
        Connection con = null;
        Statement stmt = null;
        ResultSet rs = null;

        ArrayList result = null;

        try {

            con = jdbcFactory.getConnection();
            stmt = con.createStatement();

            String query = "SELECT seq, zipcode, sido, gugun, dong, ri, bunji FROM zipcode WHERE dong LIKE '%" + dong + "%'";

            System.out.println( "> ZipcodeDAOOracle.getZipList( String ) : QUERY - " + query );
            rs = stmt.executeQuery( query );

            result = new ArrayList();
            ZipcodeTO data = null;
           
            while ( rs.next() ) {

                data = new ZipcodeTO();
            
                data.setSeq( rs.getString( "seq" ) );
                data.setZipcode( rs.getString( "zipcode" ) );
                data.setSido( rs.getString( "sido" ) );
                data.setGugun( rs.getString( "gugun" ) );
                data.setDong( rs.getString( "dong" ) );
                data.setRi( rs.getString( "ri" ) );
                data.setBunji( rs.getString( "bunji" ) );

                result.add( data );

            } // end while
           	System.out.println( " data ===" + data );		//
        } catch ( SQLException sqle ) {

            throw sqle;

        } finally {

            if ( rs != null ) rs.close();
            if ( stmt != null ) stmt.close();
            if ( con != null ) con.close();

        } // end try~catch~finally

        return result;
	}

} // end class